﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Data;
using System.Globalization;
using System.Windows.Forms;
using System.Collections.ObjectModel;


namespace University.Workshop.DataAccess
{
    public class ProductRegister: IDisposable
    {
        private const string ConectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=DataBaseHotel;Integrated Security=True";
        private SqlConnection conection;
        private SqlDataAdapter daProduct;
        public  bool ExistProduct(string idProduct)
        {
            bool ok;
            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = "SELECT [idProduct] FROM [DataBaseHotel].[dbo].[Product] WHERE idProduct=@idProduct ";
            command.Parameters.AddWithValue("@idProduct", idProduct);
            conection.Open();
            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
                ok = true;
            else
                ok = false;
            return ok;
        }
        public  void RegisterProduct(Product product)
        {
            if (product == null)
            {
                throw new ArgumentException("Invalid input parameters");
            }
            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = @"insert into [DataBaseHotel].[dbo].[Product](
                                    [IdProduct],
                                    [Group],
                                    [NameProduct],
                                    [CostBuy],
                                    [CostSale],
                                    [Amount],
                                    [Date])
                                    values
                                    (@IdProduct,
                                    @Group,
                                    @NameProduct,
                                    @CostBuy,
                                    @Costsale,
                                    @Amount,
                                    @Date)";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@IdProduct", product.IdProduct);
            command.Parameters.AddWithValue("@Group", product.Group);
            command.Parameters.AddWithValue("@NameProduct", product.NameProduct);
            command.Parameters.AddWithValue("@CostBuy", product.CostBuy);
            command.Parameters.AddWithValue("@CostSale", product.CostSale);
            command.Parameters.AddWithValue("@Amount", product.Amount);
            command.Parameters.AddWithValue("@Date", product.DateSale);
            
            try
            {
                conection.Open();
                int value = command.ExecuteNonQuery();
                if (value < 1)
                    throw new ArgumentException("the record was not registered");
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error creating product"+e.Message);
            }
        }

        public DataTable SeekProduct(string product)
        {
            DataSet dsProduct = new DataSet();
            dsProduct.Dispose();
            dsProduct.Locale = CultureInfo.InvariantCulture;
            conection = new SqlConnection(ConectionString);
            SqlCommand cmd = conection.CreateCommand();
            cmd.CommandText = "select * from [DataBaseHotel].[dbo].[Product] where [NameProduct] like @Product+'%'";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Product",product);
            daProduct = new SqlDataAdapter(cmd);
            dsProduct.Clear();
            daProduct.Fill(dsProduct, "Product");
            return dsProduct.Tables["Product"];
        }
        public DataTable SearchProductByGroup(string group)
        {
            DataSet dsProduct = new DataSet();
            dsProduct.Dispose();
            dsProduct.Locale = CultureInfo.InvariantCulture;
            conection = new SqlConnection(ConectionString);
            SqlCommand cmd = conection.CreateCommand();
            cmd.CommandText = "select [DataBaseHotel].[dbo].[Product].[NameProduct],[DataBaseHotel].[dbo].[Product].[CostSale] from [DataBaseHotel].[dbo].[Product] where [Group] = @Group";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Group", group);
            daProduct = new SqlDataAdapter(cmd);
            dsProduct.Clear();
            daProduct.Fill(dsProduct, "Product");
            return dsProduct.Tables["Product"];
        }
      
        #region Miembros de IDisposable

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (conection != null|| daProduct != null)
                {
                    conection.Dispose();
                    conection = null;
                    daProduct.Dispose();
                    daProduct = null;
                }
            }
        }

        #endregion

        public Product ViewUpdateProduct(string idProduct)
        {
            Product productUpdate;
            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = @"SELECT * FROM [DataBaseHotel].[dbo].[Product] WHERE  IdProduct = @IdProduct";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@IdProduct", idProduct);            
            try
            {
                conection.Open();
                SqlDataReader read = command.ExecuteReader();
                if (!read.HasRows)
                {
                    return null;
                }
                read.Read();
                productUpdate = new Product
                {
                    IdProduct = read["IdProduct"].ToString()
                 ,
                    Group = read["Group"].ToString()
                 ,
                    NameProduct = read["NameProduct"].ToString()
                 ,
                    CostBuy = read["CostBuy"].ToString()
                 ,
                    CostSale = read["CostSale"].ToString()
                 ,
                    Amount = read["Amount"].ToString()
                 ,
                    DateSale = read["Date"].ToString()
                };
                return productUpdate;
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
        }

        public void GetAllProducts(DataGridView products)
        {
            if (products == null)
                throw new ArgumentException("Invalid Input Parameters");

            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = "SELECT * FROM [DataBaseHotel].[dbo].[Product]";
            command.CommandType = CommandType.Text;
            if (command.Connection.State == ConnectionState.Closed)
            {
                command.Connection.Open();
            }
            SqlDataReader reader = command.ExecuteReader();
            int index = 0;
            try
            {
                while (reader.Read())
                {
                    products.Rows.Add();
                    products["IdProduct", index].Value = reader["IdProduct"].ToString();
                    products["Group", index].Value = reader["Group"].ToString();
                    products["NameProduct", index].Value = reader["NameProduct"].ToString();
                    products["CostBuy", index].Value = reader["CostBuy"].ToString();
                    products["CostSale", index].Value = reader["CostSale"].ToString();
                    products["Amount", index].Value = reader["Amount"].ToString();
                    products["Date", index].Value = reader["Date"].ToString();
                    
                    ++index;
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            if (command.Connection.State == ConnectionState.Open)
            {
                command.Connection.Close();
            }
        }

        public void UpdateProductsDataAccess(string newIdProduct, Product product)
        {
            if (product == null)
            {
                throw new ArgumentException("Invalid input parameters");
            }
            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = @"UPDATE [DataBaseHotel].[dbo].[Product] 
                                    SET    [IdProduct]= @IdProduct,
                                           [Group]= @Group,
                                           [NameProduct]= @NameProduct,
                                           [CostBuy] = @CostBuy,
                                           [CostSale]= @CostSale,
                                           [Amount]= @Amount,
                                           [Date] = @Date
                                    WHERE  IdProduct=@NewIdProduct";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@IdProduct",product.IdProduct);
            command.Parameters.AddWithValue("@Group", product.Group);
            command.Parameters.AddWithValue("@NameProduct", product.NameProduct);
            command.Parameters.AddWithValue("@CostBuy",Convert.ToInt32(product.CostBuy,CultureInfo.InvariantCulture));
            command.Parameters.AddWithValue("@CostSale",Convert.ToInt32(product.CostSale,CultureInfo.InvariantCulture));
            command.Parameters.AddWithValue("@Amount",Convert.ToInt32(product.Amount,CultureInfo.InvariantCulture));
            command.Parameters.AddWithValue("@Date", product.DateSale);
            command.Parameters.AddWithValue("@NewIdProduct", newIdProduct);

            try
            {
                conection.Open();
                int value = command.ExecuteNonQuery();
                if (value < 1)
                    throw new ArgumentException("the record was not registered");
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Modify product", e);
            }
        }
        public void DeleteProduct(string deleteIdProduct)
        {
            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = @"DELETE FROM [DataBaseHotel].[dbo].[Product]
                                           WHERE IdProduct=@IdProduct";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@IdProduct", deleteIdProduct);
            try
            {
                conection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Delete product", e);
            } 
        }
        public void RegisterSale(Sale sale)
        {
            if (sale == null)
            {
                throw new ArgumentException("Invalid input parameters");
            }
            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = @"insert into [DataBaseHotel].[dbo].[Sale](
                                    [id],
                                    [Room],
                                    [CostSaleProducts],
                                    [Description],
                                    [DateOfSale])
                                    values
                                    (@Id,
                                    @Room,
                                    @CostSaleProducts,
                                    @Description,
                                    @Date)";
            command.CommandType = CommandType.Text;
            //command.Parameters.AddWithValue("@IdSale",sale.IdSale);
            command.Parameters.AddWithValue("@Id", sale.Id);
            command.Parameters.AddWithValue("@Room", sale.IdRoom);
            command.Parameters.AddWithValue("@CostSaleProducts", (float)Convert.ToDecimal(sale.CostSaleProducts,CultureInfo.InvariantCulture));
            command.Parameters.AddWithValue("@Description", sale.Description);
            command.Parameters.AddWithValue("@Date", sale.DateOfSale);
            try
            {
                conection.Open();
                command.ExecuteNonQuery(); 
            }
            catch (Exception e)
            {
                throw new ArgumentException("Error Registerin A Sale" + e.Message);
            }
        }

        public float GetCost(string roomName, DateTime checkIn, DateTime checkout)
        {
            conection = new SqlConnection(ConectionString);
            float parcialCost = 0;
            string sqlSentence = @"SELECT * From [DataBaseHotel].[dbo].[Sale] WHERE Room=@room AND 
                                    DateOfSale  BETWEEN @CheckIn AND @CheckOut ";

            SqlCommand cmd = conection.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@CheckIn", checkIn);
            cmd.Parameters.AddWithValue("@CheckOut", checkout);
            cmd.Parameters.AddWithValue("@room", roomName);
            try
            {
                conection.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        parcialCost += (float)Convert.ToDecimal(reader["CostSaleProducts"].ToString(),CultureInfo.InvariantCulture);
                        //parcialCost += reader["CostSaleProducts"].
                    }
                }

            }
            catch (Exception e)
            {
                throw new ArgumentException(" Error getting parcialCost" + e.Message);

            }
            finally
            {
                if (conection.State == System.Data.ConnectionState.Open)
                {
                    conection.Close();
                }
            }
            return parcialCost;
        }
        public Collection<Sale> GetAllSaleClient(string idClient)
        {
            Collection<Sale> sales = new Collection<Sale>();
            conection = new SqlConnection(ConectionString);
            SqlCommand command = conection.CreateCommand();
            command.CommandText = @"SELECT distinct s.[IdSale]
                                                  ,s.[id]
                                                  ,s.[Room]
                                                  ,s.[CostSaleProducts]
                                                  ,s.[Description]
                                                  ,s.[DateOfSale] 
                                          FROM [DataBaseHotel].[dbo].[Sale] s, [DataBaseHotel].[dbo].[TokenRooms] t 
                                          WHERE s.id=@client and s.id = t.[ClientCI] and t.[CheckIn]<=@date ";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@client", idClient);
            command.Parameters.AddWithValue("@date", DateTime.Today);
            if (command.Connection.State == ConnectionState.Closed)
            {
                command.Connection.Open();
            }
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    sales.Add(new Sale { Id = reader["id"].ToString(), CostSaleProducts = reader["CostSaleProducts"].ToString(), DateOfSale = Convert.ToDateTime(reader["DateOfSale"].ToString(),CultureInfo.InvariantCulture), Description = reader["Description"].ToString() });
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            if (command.Connection.State == ConnectionState.Open)
            {
                command.Connection.Close();
            }
            return sales;
        }
    }
}
